﻿#include "dbtools.h"
#include <QSqlQuery>
#include <QVariant>
#include <QDateTime>


Dbtools::Dbtools(QObject *parent) : QObject(parent)
{
    db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("data.db");
    db.open();


    initDb();
}

QSqlDatabase Dbtools::getDb()
{
    return db;
}

void Dbtools::initDb()
{
    QString typeSql = "SELECT count(*) num from sqlite_master where type='table' and name='mytypes'";
    QString itemSql = "SELECT count(*) num from sqlite_master where type='table' and name='myitems'";
    QString settingSql = "SELECT count(*) num from sqlite_master where type='table' and name='sys_setting'";
    QString createMyItemsSql = "CREATE TABLE 'myitems' ('id'  TEXT NOT NULL,'account'  TEXT,"
                               "'pass'  TEXT,'firsttime'  TEXT,'lasttime'  TEXT,'type_id'  TEXT,"
                               "'beizhu'  TEXT,'sync_time'  TEXT,'synced'  TEXT,'del' TEXT,"
                               "PRIMARY KEY ('id' ASC))";
    QString createMyTypesSql = "CREATE TABLE 'mytypes' ('id'  TEXT NOT NULL,'type_name'  TEXT,"
                               "'firsttime'  TEXT,'lasttime'  TEXT,'icon'  TEXT,'sync_time'  TEXT,"
                               "'synced'  TEXT,'del' TEXT,PRIMARY KEY ('id' ASC))";
    QString createSettingSql = "CREATE TABLE sys_setting('mainpass' TEXT(512),'firstStart' TEXT,"
                               "'cloudAccount' TEXT,'cloudServerAddress' TEXT,'cloudServerPort' TEXT)";
    QString insertType1 = "insert into mytypes (id,type_name,firsttime,lasttime,synced,del) values "
                          "('a','工作',:firsttime,:lasttime,'N','N')";


    bool createMyTypes = false;
    bool createMyItems = false;
    bool createSetting = false;
    QSqlQuery query(db);
    query.prepare(typeSql);
    query.exec();
    if(query.next()){
        if(query.value("num").toInt()==0){
            createMyTypes = true;
        }
    }
    query.clear();

    query.prepare(itemSql);
    query.exec();
    if(query.next()){
        if(query.value("num").toInt()==0){
            createMyItems = true;
        }
    }
    query.clear();

    query.prepare(settingSql);
    query.exec();
    if(query.next()){
        if(query.value("num").toInt()==0){
            createSetting = true;
        }
    }
    query.clear();

    if(createMyItems){
        query.exec(createMyItemsSql);
    }
    if(createMyTypes){
        query.exec(createMyTypesSql);

        query.clear();
        query.prepare(insertType1);
        query.bindValue(":firsttime",tr("%1").arg(QDateTime::currentDateTime().toMSecsSinceEpoch()));
        query.bindValue(":lasttime",tr("%1").arg(QDateTime::currentDateTime().toMSecsSinceEpoch()));
        query.exec();

    }
    if(createSetting){
        query.exec(createSettingSql);
        query.clear();
        query.prepare("insert into sys_setting (mainpass,firstStart) values (:mainpass,'Y')");
        query.bindValue(":mainpass","-1");;
        query.exec();
    }
    query.clear();

}
